#!/usr/bin/perl -w

BEGIN {
	push(@INC,"/home/modules/");
};
use strict;
use utf8;
use Excel::Writer::XLSX;
use Text::Iconv;
use Encode;
use DBI;
use Cwd 'abs_path';

abs_path($0)=~/^(.*[\\\/])/;
my $logpath="$1/logs/xls_export.log";
sub mywarn {
	open OUT,">>$logpath" or print STDERR "Could not write to $logpath\n";
	foreach (@_) {
		print OUT $_;
		print OUT "\n" unless $_=~/\n$/;
#		print STDERR $_;
#		print STDERR "\n" unless $_=~/\n$/;
	}
	close OUT;
}

sub mydie {
	mywarn(@_);
	exit(1);
}

# Parameters - login, password, database, ss_id, $output_filename

{
	$SIG{__WARN__}=\&mywarn;
	$SIG{__DIE__}=\&mydie;
	$SIG{INT}=\&mydie;

	warn "\n\n".localtime()."\n";
	warn $0." ".join(" ",@ARGV)."\n";
	die "Wrong parameters" unless @ARGV==5;
	my ($login,$password,$database,$ss_id,$filename)=@ARGV;
	
	my $dbh=DBI->connect("dbi:Pg:dbname=$database;host=127.0.0.1",$login,$password, {AutoCommit => 0});
	my $sth1=$dbh->prepare("select * from Tspreadsheets where id=?");
	my $sth2=$dbh->prepare("select * from Tworksheets where spreadsheet_id=? order by ordering");
	my $sth3=$dbh->prepare("select * from Tcells where worksheet_id=? order by y,x");
	
	$sth1->execute($ss_id);
	my $ss=$sth1->fetchrow_hashref();
	
	#my $filename=$ARGV[;#"/tmp/lattenoir-".$ss->{name}.".xlsx";
	if (-f $filename) {
		unlink($filename) or die "Can not delete $filename";
	}
	my $workbook = Excel::Writer::XLSX->new($filename);
	$sth2->execute($ss_id);
	while (my $ws=$sth2->fetchrow_hashref()) {
		my $worksheet = $workbook->add_worksheet( $ws->{name});
		$sth3->execute($ws->{id});
		while (my $cell=$sth3->fetchrow_hashref()) {
			Encode::_utf8_on($cell->{value});
			$worksheet->write($cell->{y},$cell->{x},$cell->{value});
		}
	}
	$dbh->commit();
}

